﻿Imports System.Data.SqlClient

Public Class frmChartofAccountsMng

    Sub FillTree()
        Try
            Me.Cursor = Cursors.WaitCursor
            Dim cmd As New SqlCommand("Select Acc1 From Acc Where Acc1 Is Not Null and Acc1<>N'' and AccNo2=0", cnn)
            Dim Reader, Reader1, Reader2, Reader3 As SqlDataReader
            Dim i, i1, i2 As Integer

            Me.TreeAcc.Nodes.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.TreeAcc.Nodes.Add(Reader.Item(0))
                Dim cmd1 As New SqlCommand("Select Acc2 From Acc Where Acc1=N'" & Reader.Item(0) & _
                                           "' and Acc2 Is Not Null and Acc2<>N'' and AccNo3=0", cnn1)

                cnn1.Open()
                Reader1 = cmd1.ExecuteReader
                While Reader1.Read
                    Me.TreeAcc.Nodes(i).Nodes.Add(Reader1.Item(0))
                    Dim cmd2 As New SqlCommand("Select Acc3 From Acc Where Acc1=N'" & Reader.Item(0) & "' and " & _
                                               "Acc2=N'" & Reader1.Item(0) & "' and Acc3 Is Not Null and Acc3<>N'' and AccNo4=0", cnn2)

                    cnn2.Open()
                    Reader2 = cmd2.ExecuteReader
                    While Reader2.Read
                        Me.TreeAcc.Nodes(i).Nodes(i1).Nodes.Add(Reader2.Item(0))
                        Dim cmd3 As New SqlCommand("Select Acc4 From Acc Where Acc1=N'" & Reader.Item(0) & "' and " & _
                                                  "Acc2=N'" & Reader1.Item(0) & "' and Acc3=N'" & Reader2.Item(0) & _
                                                  "' and Acc4 Is Not Null and Acc4<>N'' and AccNo4<>0", cnn3)
                        cnn3.Open()
                        Reader3 = cmd3.ExecuteReader
                        While Reader3.Read
                            Me.TreeAcc.Nodes(i).Nodes(i1).Nodes(i2).Nodes.Add(Reader3.Item(0))
                        End While
                        cnn3.Close()
                        i2 += 1
                    End While
                    cnn2.Close()
                    i2 = 0
                    i1 += 1
                End While

                cnn1.Close()
                i2 = 0
                i1 = 0
                i += 1
            End While
            cnn.Close()
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            If cnn1.State = ConnectionState.Open Then
                cnn1.Close()
            End If
            If cnn2.State = ConnectionState.Open Then
                cnn2.Close()
            End If
            If cnn3.State = ConnectionState.Open Then
                cnn3.Close()
            End If
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddGroup.Click
        Dim a As New frmAddAccount
        a.CurrentLevel = 0
        a.FillNewAccNo()

        a.ShowDialog()

        If a.Saved = True Then
            Me.TreeAcc.Nodes.Add(a.AccountName)
        End If
    End Sub

    Sub Clear()
        Try
            Me.txtAcc1.Clear()
            Me.txtAcc2.Clear()
            Me.txtAcc3.Clear()
            Me.txtAcc4.Clear()
            Me.txtAccNo.Clear()
            Me.txtBalance.Clear()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub TreeAcc_AfterLabelEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.NodeLabelEditEventArgs) Handles TreeAcc.AfterLabelEdit
        Try
            If Me.TreeAcc.SelectedNode.Level = -1 Then
                Exit Sub
            End If
            If e.Label = "" Then
                Exit Sub
            Else
                Me.Cursor = Cursors.WaitCursor
                Dim StrIns, StrIns1 As String

                Select Case Me.TreeAcc.SelectedNode.Level
                    Case 0
                        StrIns = "Update Acc set Acc1= N'" & _
                        e.Label.ToString & "' where Acc1 ='" & e.Node.Text & "'"
                        StrIns1 = "Update Transactions set Acc1= N'" & _
                        e.Label.ToString & "' where Maingroup ='" & e.Node.Text & "'"
                    Case 1
                        StrIns = "Update Acc set Acc2=N'" & _
                        e.Label.ToString & _
                        "'where Acc1=N'" & Me.TreeAcc.SelectedNode.Parent.Text & _
                        "'and Acc2=N'" & e.Node.Text & "'"
                        StrIns1 = "Update Transactions set Acc2=N'" & _
                       e.Label.ToString & _
                       "'where Acc1=N'" & Me.TreeAcc.SelectedNode.Parent.Text & _
                       "'and Acc2=N'" & e.Node.Text & "'"
                    Case 2
                        StrIns = "Update Acc set Acc3=N'" & e.Label.ToString & _
                        "'Where Acc1=N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Text & _
                        "' and Acc2=N'" & Me.TreeAcc.SelectedNode.Parent.Text & _
                        "' and Acc3 =N'" & e.Node.Text & "'"
                        StrIns1 = "Update Transactions set Acc3=N'" & e.Label.ToString & _
                        "'Where Acc1=N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Text & _
                        "' and Acc2=N'" & Me.TreeAcc.SelectedNode.Parent.Text & _
                        "' and Acc3 =N'" & e.Node.Text & "'"
                    Case 3
                        StrIns = "Update Acc set Acc4 =N'" & e.Label.ToString & _
                        "'Where Acc1 =N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Parent.Text & _
                        "' and Acc2=N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Text & _
                        "' and Acc3 =N'" & Me.TreeAcc.SelectedNode.Parent.Text & _
                        "' and Acc4 =N'" & e.Node.Text & "'"
                        StrIns1 = "Update Transactions set Acc4 =N'" & e.Label.ToString & _
                       "'Where Acc1 =N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Parent.Text & _
                       "' and Acc2=N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Text & _
                       "' and Acc3 =N'" & Me.TreeAcc.SelectedNode.Parent.Text & _
                       "' and Acc4 =N'" & e.Node.Text & "'"
                    Case 4
                        StrIns = "Update Acc set Acc5 =N'" & e.Label.ToString & _
                        "'Where Acc1 =N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Parent.Parent.Text & _
                        "' and Acc2=N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Parent.Text & _
                        "' and Acc3 =N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Text & _
                        "' and Acc4 =N'" & Me.TreeAcc.SelectedNode.Parent.Text & _
                        "' and Acc5 =N'" & e.Node.Text & "'"
                        StrIns1 = "Update Transactions set Acc5 =N'" & e.Label.ToString & _
                       "'Where Acc1 =N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Parent.Parent.Text & _
                       "' and Acc2=N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Parent.Text & _
                       "' and Acc3 =N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Text & _
                       "' and Acc4 =N'" & Me.TreeAcc.SelectedNode.Parent.Text & _
                       "' and Acc5 =N'" & e.Node.Text & "'"
                End Select

                Dim cmd As New SqlCommand(StrIns, cnn)
                Dim cmd1 As New SqlCommand(StrIns1, cnn)
                cnn.Open()
                cmd.ExecuteNonQuery()
                cnn.Close()

                'FillTree()

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub TreeAcc_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeAcc.AfterSelect
        Clear()
        Try
            If Me.TreeAcc.SelectedNode.Level = -1 Then
                Me.btnAddGroup.Enabled = True
                Me.btnAddSub.Enabled = True
            ElseIf Me.TreeAcc.SelectedNode.Level = 3 Then
                Me.btnAddGroup.Enabled = True
                Me.btnAddSub.Enabled = False
            Else
                Me.btnAddGroup.Enabled = True
                Me.btnAddSub.Enabled = True
            End If

            Try
                If Me.TreeAcc.SelectedNode.Level = -1 Then
                    Exit Sub
                Else
                    Me.Cursor = Cursors.Default
                    Dim Balance As Double
                    Select Case Me.TreeAcc.SelectedNode.Level
                        Case 0
                            Me.txtAcc1.Text = Me.TreeAcc.SelectedNode.Text
                            Me.txtAccNo.Text = GetAccNo(Me.txtAcc1.Text.ToString)
                            Balance = GetBalanceAcc(Me.txtAcc1.Text.ToString)

                            If Balance < 0 Then
                                Me.txtBalance.Text = "(" & Format((-1 * Balance), "##,###.##") & ")"
                            ElseIf Balance = 0 Then
                                Me.txtBalance.Text = "0.0"
                            Else
                                Me.txtBalance.Text = Format(Balance, "##,###.##")
                            End If
                        Case 1
                            Me.txtAcc1.Text = Me.TreeAcc.SelectedNode.Parent.Text.ToString
                            Me.txtAcc2.Text = Me.TreeAcc.SelectedNode.Text.ToString
                            Me.txtAccNo.Text = GetAccNo(Me.txtAcc1.Text.ToString, Me.txtAcc2.Text.ToString)
                            Balance = GetBalanceAcc(Me.txtAcc1.Text.ToString, Me.txtAcc2.Text.ToString)
                            If Balance < 0 Then
                                Me.txtBalance.Text = "(" & Format((-1 * Balance), "##,###.##") & ")"
                            ElseIf Balance = 0 Then
                                Me.txtBalance.Text = "0.0"
                            Else
                                Me.txtBalance.Text = Format(Balance, "##,###.##")
                            End If
                        Case 2
                            Me.txtAcc1.Text = Me.TreeAcc.SelectedNode.Parent.Parent.Text.ToString
                            Me.txtAcc2.Text = Me.TreeAcc.SelectedNode.Parent.Text.ToString
                            Me.txtAcc3.Text = Me.TreeAcc.SelectedNode.Text.ToString
                            Me.txtAccNo.Text = GetAccNo(Me.txtAcc1.Text.ToString, Me.txtAcc2.Text.ToString, Me.txtAcc3.Text.ToString)
                            Balance = GetBalanceAcc(Me.txtAcc1.Text.ToString, Me.txtAcc2.Text.ToString, Me.txtAcc3.Text.ToString)
                            If Balance < 0 Then
                                Me.txtBalance.Text = "(" & Format((-1 * Balance), "##,###.##") & ")"
                            ElseIf Balance = 0 Then
                                Me.txtBalance.Text = "0.0"
                            Else
                                Me.txtBalance.Text = Format(Balance, "##,###.##")
                            End If
                        Case 3
                            Me.txtAcc1.Text = Me.TreeAcc.SelectedNode.Parent.Parent.Parent.Text.ToString
                            Me.txtAcc2.Text = Me.TreeAcc.SelectedNode.Parent.Parent.Text.ToString
                            Me.txtAcc3.Text = Me.TreeAcc.SelectedNode.Parent.Text.ToString
                            Me.txtAcc4.Text = Me.TreeAcc.SelectedNode.Text.ToString
                            Me.txtAccNo.Text = GetAccNo(Me.txtAcc1.Text.ToString, Me.txtAcc2.Text.ToString, _
                                                        Me.txtAcc3.Text.ToString, Me.txtAcc4.Text.ToString)
                            Balance = GetBalanceAcc(Me.txtAcc1.Text.ToString, Me.txtAcc2.Text.ToString, _
                                                    Me.txtAcc3.Text.ToString, Me.txtAcc4.Text.ToString)
                            If Balance < 0 Then
                                Me.txtBalance.Text = "(" & Format((-1 * Balance), "##,###.##") & ")"
                            ElseIf Balance = 0 Then
                                Me.txtBalance.Text = "0.0"
                            Else
                                Me.txtBalance.Text = Format(Balance, "##,###.##")
                            End If
                    End Select
                    Me.Cursor = Cursors.Default
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmMngChrtOfAcc_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillTree()
    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddSub.Click
        Try
            If Me.TreeAcc.SelectedNode.Index = -1 Then
                Exit Sub
            Else
                Dim a As New frmAddAccount
                Select Case Me.TreeAcc.SelectedNode.Level
                    Case 0
                        a.Acc1 = Me.TreeAcc.SelectedNode.Text
                        a.AccNo1 = Me.txtAccNo.Text.Substring(0, 1)
                        a.CurrentLevel = 1
                        a.FillNewAccNo()

                    Case 1
                        a.Acc1 = Me.TreeAcc.SelectedNode.Parent.Text
                        a.Acc2 = Me.TreeAcc.SelectedNode.Text

                        a.AccNo1 = Me.txtAccNo.Text.Substring(0, 1)
                        a.AccNo2 = Me.txtAccNo.Text.Substring(1, 1)

                        a.CurrentLevel = 2
                        a.FillNewAccNo()

                    Case 2
                        a.Acc1 = Me.TreeAcc.SelectedNode.Parent.Parent.Text
                        a.Acc2 = Me.TreeAcc.SelectedNode.Parent.Text
                        a.Acc3 = Me.TreeAcc.SelectedNode.Text

                        a.AccNo1 = Me.txtAccNo.Text.Substring(0, 1)
                        a.AccNo2 = Me.txtAccNo.Text.Substring(1, 1)
                        a.AccNo3 = Me.txtAccNo.Text.Substring(2, 2)

                        a.CurrentLevel = 3
                        a.FillNewAccNo()

                        'Case 3
                        '    a.Acc1 = Me.TreeAcc.SelectedNode.Parent.Parent.Parent.Text
                        '    a.Acc2 = Me.TreeAcc.SelectedNode.Parent.Parent.Text
                        '    a.Acc3 = Me.TreeAcc.SelectedNode.Parent.Text
                        '    a.Acc4 = Me.TreeAcc.SelectedNode.Text

                        '    a.AccNo1 = Me.txtAccNo.Text.Substring(0, 1)
                        '    a.AccNo2 = Me.txtAccNo.Text.Substring(1, 1)
                        '    a.AccNo3 = Me.txtAccNo.Text.Substring(2, 2)
                        '    a.AccNo4 = Me.txtAccNo.Text.Substring(4, 2)

                        '    a.CurrentLevel = 4
                        '    a.FillNewAccNo()
                End Select
                a.ShowDialog()

                If a.Saved = True Then
                    Me.TreeAcc.SelectedNode.Nodes.Add(a.AccountName)
                End If
                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub ToolStripButton3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            If Me.TreeAcc.SelectedNode.Level = -1 OrElse Me.txtBalance.Text.Trim.Length = 0 Then
                Exit Sub
            ElseIf CInt(Me.txtBalance.Text) <> 0 Then
                MsgBox("The account has balance")
                Exit Sub
            ElseIf Me.TreeAcc.SelectedNode.Nodes.Count > 0 Then
                MsgBox("Please delete all sub accounts")
                Exit Sub
            Else
                If MsgBox("Confirm Delete?", MsgBoxStyle.YesNoCancel) = MsgBoxResult.Yes Then
                    Me.Cursor = Cursors.WaitCursor
                    Dim StrIns As String

                    Select Case Me.TreeAcc.SelectedNode.Level
                        Case 0
                            StrIns = "Delete From Acc Where Acc1=N'" & Me.TreeAcc.SelectedNode.Text & "'"
                        Case 1
                            StrIns = "Delete From Acc Where Acc1=N'" & Me.TreeAcc.SelectedNode.Parent.Text & _
                                     "' and Acc2=N'" & Me.TreeAcc.SelectedNode.Text & "'"
                        Case 2
                            StrIns = "Delete From Acc Where Acc1=N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Text & _
                                     "' and Acc2=N'" & Me.TreeAcc.SelectedNode.Parent.Text & _
                                     "' and Acc3=N'" & Me.TreeAcc.SelectedNode.Text & "'"
                        Case 3
                            StrIns = "Delete From Acc Where Acc1=N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Parent.Text & _
                                     "' and Acc2=N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Text & _
                                     "' and Acc3=N'" & Me.TreeAcc.SelectedNode.Parent.Text & "' and Acc4=N'" & Me.TreeAcc.SelectedNode.Text & "'"
                        Case 4
                            StrIns = "Delete From Acc Where Acc1=N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Parent.Parent.Text & _
                                    "' and Acc2=N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Parent.Text & _
                                    "' and Acc3=N'" & Me.TreeAcc.SelectedNode.Parent.Parent.Text & _
                                    "' and Acc4=N'" & Me.TreeAcc.SelectedNode.Parent.Text & "' and Acc5=N'" & Me.TreeAcc.SelectedNode.Text & "'"

                    End Select
                    Dim cmd As New SqlCommand(StrIns, cnn)

                    cnn.Open()
                    cmd.ExecuteNonQuery()
                    cnn.Close()

                    Me.TreeAcc.SelectedNode.Remove()
                    Clear()

                    Me.Cursor = Cursors.Default
                End If
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    'Private Sub TreeAcc_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles TreeAcc.DoubleClick
    '    Clear()
    '    Try
    '        If Me.TreeAcc.SelectedNode.Level = -1 Then
    '            Exit Sub
    '        Else
    '            Me.Cursor = Cursors.Default
    '            Dim Balance As Double
    '            Select Case Me.TreeAcc.SelectedNode.Level
    '                Case 0
    '                    Me.txtAcc1.Text = Me.TreeAcc.SelectedNode.Text
    '                    Balance = GetBalanceAcc(Me.txtAcc1.Text.ToString)

    '                    If Balance < 0 Then
    '                        Me.txtBalance.Text = "(" & Format((-1 * Balance), "##,###.##") & ")"
    '                    ElseIf Balance = 0 Then
    '                        Me.txtBalance.Text = "0.0"
    '                    Else
    '                        Me.txtBalance.Text = Format(Balance, "##,###.##")
    '                    End If
    '                Case 1
    '                    Me.txtAcc1.Text = Me.TreeAcc.SelectedNode.Parent.Text.ToString
    '                    Me.txtAcc2.Text = Me.TreeAcc.SelectedNode.Text.ToString

    '                    Balance = GetBalanceAcc(Me.txtAcc1.Text.ToString, Me.txtAcc2.Text.ToString)
    '                    If Balance < 0 Then
    '                        Me.txtBalance.Text = "(" & Format((-1 * Balance), "##,###.##") & ")"
    '                    ElseIf Balance = 0 Then
    '                        Me.txtBalance.Text = "0.0"
    '                    Else
    '                        Me.txtBalance.Text = Format(Balance, "##,###.##")
    '                    End If
    '                Case 2
    '                    Me.txtAcc1.Text = Me.TreeAcc.SelectedNode.Parent.Parent.Text.ToString
    '                    Me.txtAcc2.Text = Me.TreeAcc.SelectedNode.Parent.Text.ToString
    '                    Me.txtAcc3.Text = Me.TreeAcc.SelectedNode.Text.ToString

    '                    Balance = GetBalanceAcc(Me.txtAcc1.Text.ToString, Me.txtAcc2.Text.ToString, Me.txtAcc3.Text.ToString)
    '                    If Balance < 0 Then
    '                        Me.txtBalance.Text = "(" & Format((-1 * Balance), "##,###.##") & ")"
    '                    ElseIf Balance = 0 Then
    '                        Me.txtBalance.Text = "0.0"
    '                    Else
    '                        Me.txtBalance.Text = Format(Balance, "##,###.##")
    '                    End If
    '                Case 3
    '                    Me.txtAcc1.Text = Me.TreeAcc.SelectedNode.Parent.Parent.Parent.Text.ToString
    '                    Me.txtAcc2.Text = Me.TreeAcc.SelectedNode.Parent.Parent.Text.ToString
    '                    Me.txtAcc3.Text = Me.TreeAcc.SelectedNode.Parent.Text.ToString
    '                    Me.txtAcc4.Text = Me.TreeAcc.SelectedNode.Text.ToString

    '                    Balance = GetBalanceAcc(Me.txtAcc1.Text.ToString, Me.txtAcc2.Text.ToString, _
    '                                            Me.txtAcc3.Text.ToString, Me.txtAcc4.Text.ToString)
    '                    If Balance < 0 Then
    '                        Me.txtBalance.Text = "(" & Format((-1 * Balance), "##,###.##") & ")"
    '                    ElseIf Balance = 0 Then
    '                        Me.txtBalance.Text = "0.0"
    '                    Else
    '                        Me.txtBalance.Text = Format(Balance, "##,###.##")
    '                    End If
    '            End Select
    '            Me.Cursor = Cursors.Default
    '        End If
    '    Catch ex As Exception
    '        Me.Cursor = Cursors.Default
    '        If cnn.State = ConnectionState.Open Then
    '            cnn.Close()
    '        End If
    '        MsgBox(ex.ToString)
    '    End Try
    'End Sub

    Private Sub ToolStripButton4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click
        FillTree()
    End Sub
End Class